package yicr.untils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import yicr.model.MaterialInfoBo;
import yicr.model.MaterialUnifiedRecordBo;
import yicr.model.NslPointInt;
import yicr.model.ProjectInfoBo;
import yicr.model.excel.ExcelOutputTemplate;
import yicr.model.excel.ExcelStringRowTable;
import yicr.model.excel.ExcelTemplate;
import yicr.model.excel.FillTemplateExcelGroupRowsHandler;


import java.util.*;
import java.util.stream.Collectors;


public class MaterialAuditServiceImpl  {


    /**
     * 最终处理
     * @param auditMaterialExcelPathList
     * @param oaMaterialExcelPath
     * @param contractSnProjectExcelPath
     * @param unifiedRecordExcelPath
     * @param compareExcelTemplatePath
     * @param outputExcelFilePath
     * @param contractSn
     */

    static public void fillMaterialInfoCompareExcelTemplate(List<String> auditMaterialExcelPathList, String oaMaterialExcelPath, String contractSnProjectExcelPath, String unifiedRecordExcelPath, String compareExcelTemplatePath, String outputExcelFilePath, String contractSn){
        //合同编号 项目信息 map
        Map<String, ProjectInfoBo>  contracSnProjectInfoMap=readProjectInfoMap(contractSnProjectExcelPath,"Sheet1",1);

        //统一对照表
        Map<String, MaterialUnifiedRecordBo> unifiedRecordMap=readUnifiedRecordMap(unifiedRecordExcelPath,"Sheet1");

        //定案材料表
        List<MaterialInfoBo> materialInfoListOfAudit=readMaterialInfoBoListFromMultipleAuditExcel(auditMaterialExcelPathList,contracSnProjectInfoMap);
        unifyMaterialList(materialInfoListOfAudit,unifiedRecordMap);

        List<String> contractSnList=new ArrayList<>();

        //oa材料表
        List<MaterialInfoBo> materialInfoListOfOa=readOaMaterialInfoOfOa(oaMaterialExcelPath,"项目物资核算台账",contractSnList);
        unifyMaterialList(materialInfoListOfOa,unifiedRecordMap);

        //构建 去重后的 统一 名称规划单位
        Set<String> unifiedItemNameModelUnitSet=buildUnifiedItemNameModelUnitOfMaterialInfo(materialInfoListOfOa,materialInfoListOfAudit);

        //填充定案 OA 材料信息
        try (ExcelWriter excelWriter = EasyExcel.write(outputExcelFilePath).withTemplate(compareExcelTemplatePath).build()) {
            WriteSheet writeSheetOfAudit = EasyExcel.writerSheet("定案表材料详表").build();
            excelWriter.fill(materialInfoListOfAudit, writeSheetOfAudit);

            WriteSheet writeSheetOfOa = EasyExcel.writerSheet("OA导出材料详表").build();
            excelWriter.fill(materialInfoListOfOa, writeSheetOfOa);
        }

        //填充 核对 sheet
        ExcelOutputTemplate excelOutputTemplate=new ExcelOutputTemplate();
        // String excelPath="Z:\\GS_Dir_Zdisk\\GS#i3#Work\\2025SJY\\公司内部支撑\\风控\\compare_material_info_4test.xlsx";
        excelOutputTemplate.setFileAbsPath(outputExcelFilePath);
        excelOutputTemplate.setSheetName("核对");
        excelOutputTemplate.setDataGroupBeginRowIndex(1);
        excelOutputTemplate.setDataGroupEndRowIndex(1);
        excelOutputTemplate.setHeaderOccupiedRowNumber(1);

//        String compareExcelOutputPath="D:\\GS_Dir_Ddisk\\GS#i3#Coding\\Test\\easy_excel\\fill\\compare_v1.xlsx";
//        compareExcelOutputPath= NslUtilFilePath.buildFilePathWithSystemTimestampAtRear(compareExcelOutputPath);

        List<String> unifiedItemNameModelUnitList=new ArrayList<>();
        unifiedItemNameModelUnitList.addAll(unifiedItemNameModelUnitSet);

        NslUtilExcelFillOutputTemplate.fillOutputTemplate(excelOutputTemplate, unifiedItemNameModelUnitList, outputExcelFilePath, new FillTemplateExcelGroupRowsHandler() {
            @Override
            public void fillGroupRows(Object o, List list) {
                Row row= (Row) list.get(0);
                Cell cell1=row.getCell(0);
                Cell cell2=row.getCell(1);


                cell1.setCellValue(contractSn);
                cell2.setCellValue((String) o);
            }
        });

        NslUtilExcelFormula.formula2Value(outputExcelFilePath,"核对");

    }

    /**
     *
     * @param auditMaterialExcelPathList
     * @param auditMaterialExcelSheetName
     * @param oaMaterialExcelPath
     * @param oaMaterialExcelSheetName
     * @param unifiedRecordExcelPath
     * @param unifiedRecordExcelSheetName
     * @param compareExcelTemplatePath
     * @param outputExcelFilePath
     */
    static public void fillMaterialInfoCompareExcelTemplateV2(List<String> auditMaterialExcelPathList,String auditMaterialExcelSheetName, String oaMaterialExcelPath,String oaMaterialExcelSheetName,  String unifiedRecordExcelPath,String unifiedRecordExcelSheetName, String compareExcelTemplatePath, String outputExcelFilePath){

        //统一对照表
        Map<String, MaterialUnifiedRecordBo> unifiedRecordMap=readUnifiedRecordMapV2(unifiedRecordExcelPath,unifiedRecordExcelSheetName,1);

        //定案材料表
        List<MaterialInfoBo> materialInfoListOfAudit=readMaterialInfoBoListFromSingleAuditExcelV2(auditMaterialExcelPathList.get(0),auditMaterialExcelSheetName);
        unifyMaterialList(materialInfoListOfAudit,unifiedRecordMap);

        //key: contractSn, value: unifiedItemNameModelUnitList
//        Map<String,List<String>> contractSnUnifiedItemNameModelUnitMap=new HashMap<>();
//        for (MaterialInfoBo materialInfoBo : materialInfoListOfAudit) {
//            String contractSn=materialInfoBo.getContractSn();
//            Set<String> keySet=contractSnUnifiedItemNameModelUnitMap.keySet();
//            if(keySet.contains(contractSn)){
//                List<String> unifiedItemList=contractSnUnifiedItemNameModelUnitMap.get(contractSn);
//                unifiedItemList.add(materialInfoBo.getUnifiedItemNameModelUnit());
//            }else{
//                List<String> unifiedItemList=new ArrayList<>();
//                unifiedItemList.add(materialInfoBo.getUnifiedItemNameModelUnit());
//                contractSnUnifiedItemNameModelUnitMap.put(contractSn,unifiedItemList);
//            }
//        }

        List<String> contractSnList=new ArrayList<>();
        Set<String> contractSnSet=materialInfoListOfAudit.stream().map(e->
            e.getContractSn()).collect(Collectors.toSet());
        contractSnList.addAll(contractSnSet);

        //oa材料表
        List<MaterialInfoBo> materialInfoListOfOa=readOaMaterialInfoOfOa(oaMaterialExcelPath,oaMaterialExcelSheetName,contractSnList);
        unifyMaterialList(materialInfoListOfOa,unifiedRecordMap);

        //构建 去重后的 统一 名称规划单位
        Set<String> unifiedItemNameModelUnitSet=buildUnifiedItemNameModelUnitOfMaterialInfo(materialInfoListOfOa,materialInfoListOfAudit);

        String separator="@#@";

        //构建 合同_统一名称规划单位 set
        List<String> contractSnUnifiedItemList=new ArrayList<>();
        for (String contractSn : contractSnSet) {
            for (String unifiedItem : unifiedItemNameModelUnitSet) {
                contractSnUnifiedItemList.add((contractSn+separator+unifiedItem));
            }
        }

        if(contractSnUnifiedItemList.size()==0){
            throw new RuntimeException("合同编号未匹配到记录，请检查合同编号");
        }

        //填充定案 OA 材料信息
        try (ExcelWriter excelWriter = EasyExcel.write(outputExcelFilePath).withTemplate(compareExcelTemplatePath).build()) {
            WriteSheet writeSheetOfAudit = EasyExcel.writerSheet("定案表材料详表").build();
            excelWriter.fill(materialInfoListOfAudit, writeSheetOfAudit);

            WriteSheet writeSheetOfOa = EasyExcel.writerSheet("OA导出材料详表").build();
            excelWriter.fill(materialInfoListOfOa, writeSheetOfOa);
        }

        //填充 核对 sheet
        ExcelOutputTemplate excelOutputTemplate=new ExcelOutputTemplate();
        // String excelPath="Z:\\GS_Dir_Zdisk\\GS#i3#Work\\2025SJY\\公司内部支撑\\风控\\compare_material_info_4test.xlsx";
        excelOutputTemplate.setFileAbsPath(outputExcelFilePath);
        excelOutputTemplate.setSheetName("核对");
        excelOutputTemplate.setDataGroupBeginRowIndex(1);
        excelOutputTemplate.setDataGroupEndRowIndex(1);
        excelOutputTemplate.setHeaderOccupiedRowNumber(1);

//        String compareExcelOutputPath="D:\\GS_Dir_Ddisk\\GS#i3#Coding\\Test\\easy_excel\\fill\\compare_v1.xlsx";
//        compareExcelOutputPath= NslUtilFilePath.buildFilePathWithSystemTimestampAtRear(compareExcelOutputPath);

//        List<String> unifiedItemNameModelUnitList=new ArrayList<>();
//        unifiedItemNameModelUnitList.addAll(unifiedItemNameModelUnitSet);

        NslUtilExcelFillOutputTemplate.fillOutputTemplate(excelOutputTemplate, contractSnUnifiedItemList, outputExcelFilePath, new FillTemplateExcelGroupRowsHandler() {
            @Override
            public void fillGroupRows(Object o, List list) {
                //O是统一名称规格单位
                //list 是合同编号

                Row row= (Row) list.get(0);
                Cell cell1=row.getCell(0);
                Cell cell2=row.getCell(1);

                String contractSnUnifiedItem= (String) o;
                String[] stringArray=contractSnUnifiedItem.split(separator);
                String contractSn=stringArray[0];
                String unifiedItem=stringArray[1];
                cell1.setCellValue(contractSn);
                cell2.setCellValue(unifiedItem);
            }
        });

       NslUtilExcelFormula.formula2Value(outputExcelFilePath,"核对");

    }


    /**
     * 读取 合同编号 和 单项名称对照表，返回map
     * key: 合同编号，value: ProjectInfoBo
     * @param excelFilePath
     * @return
     */

    static public Map<String,ProjectInfoBo>  readProjectInfoMap(String excelFilePath, String sheetName, int headLineCount){
        ExcelTemplate excelTemplate=new ExcelTemplate();
        excelTemplate.setFileAbsPath(excelFilePath);
        excelTemplate.setSheetName(sheetName);
        excelTemplate.setHeaderRowCount(0);
        int endColumnIndex=20;

        ExcelStringRowTable excelStringRowTable= NslUtilExcelRead.readDataAsLineListOfStringListIncludeHeadRows(excelTemplate,endColumnIndex);

        String contractIndexKeyWord="合同编号";
        String singleProjectNameKeyWord="单项名称";
        Set<String> keyWordSet=new HashSet<>();
        keyWordSet.add(contractIndexKeyWord);
        keyWordSet.add(singleProjectNameKeyWord);

//        Map<String, NslPointInt> xyMap= NslUtilExcelCommonUtils.findKeyWordCoordinate(keyWordSet,excelStringRowTable,headLineCount,endColumnIndex);

        List<Map<String,String>> mapList=NslUtilExcelCommonUtils.findKeyWordColumnContentMapList(keyWordSet,excelStringRowTable,headLineCount,endColumnIndex);

        Map<String,ProjectInfoBo> contractProjectInfoMap=new HashMap<>();

        for (Map<String, String> stringMap : mapList) {
            String contract=stringMap.get(contractIndexKeyWord);
            String singleProjectName=stringMap.get(singleProjectNameKeyWord);
            ProjectInfoBo projectInfoBo=null;
            if(contractProjectInfoMap.containsKey(contract)){
                projectInfoBo=contractProjectInfoMap.get(contract);
            }else{
                projectInfoBo=new ProjectInfoBo();
                projectInfoBo.setContractSn(contract);
                contractProjectInfoMap.put(contract,projectInfoBo);
            }
            projectInfoBo.getSingleProjectNameList().add(singleProjectName);
        }
        return contractProjectInfoMap;
    }


    /**
     * 读取 统一换算表 v2
     *   map:  key=原始值 ，value=统一变量
     * @param excelPath
     * @param sheetName
     * @param headRowCount
     * @return
     */
    static public Map<String, MaterialUnifiedRecordBo> readUnifiedRecordMapV2(String excelPath, String sheetName,int headRowCount){
        ExcelTemplate excelTemplate=new ExcelTemplate();
        //String excelPath="Z:\\GS_Dir_Zdisk\\GS#i3#Work\\2025SJY\\公司内部支撑\\风控\\名称规格单位统一对照表.xlsx";
        excelTemplate.setFileAbsPath(excelPath);
        excelTemplate.setSheetName(sheetName);
        excelTemplate.setHeaderRowCount(headRowCount);

        Set<String> keywordSet=new HashSet<>();
        String key1="原始名称规格单位";
        String key2="统一名称规格单位";
        String key3="原始到统一换算关系";
        keywordSet.add(key1);
        keywordSet.add(key2);
        keywordSet.add(key3);

        ExcelStringRowTable excelStringRowTable=NslUtilExcelRead.readDataAsLineListOfStringListIncludeHeadRows(excelTemplate,20);

        List<Map<String,String>> mapList=NslUtilExcelCommonUtils.findKeyWordColumnContentMapList(keywordSet,excelStringRowTable,headRowCount,20);

//        for (Map<String, String> stringMap : mapList) {
//            System.out.println(stringMap);
//        }



        Map<String,MaterialUnifiedRecordBo> unifiedRecordBoMap=new HashMap<>();
        mapList.forEach(e->{
            String original=e.get(key1);
            String unified=e.get(key2);
            String conversionRate=e.get(key3);

            MaterialUnifiedRecordBo bo=new MaterialUnifiedRecordBo();
            bo.setConversionRate(Double.parseDouble(conversionRate));
            bo.setOriginItemNameModelUnit(original);
            bo.setUnifiedItemNameModelUnit(unified);
            unifiedRecordBoMap.put(original,bo);
        });

        return unifiedRecordBoMap;
    }

    /**
     * 读取 统一换算表
     * map:  key=原始值 ，value=统一变量
     * @param excelPath
     * @param sheetName
     * @return
     */
    static public Map<String, MaterialUnifiedRecordBo> readUnifiedRecordMap(String excelPath, String sheetName){
        ExcelTemplate excelTemplate=new ExcelTemplate();
        //String excelPath="Z:\\GS_Dir_Zdisk\\GS#i3#Work\\2025SJY\\公司内部支撑\\风控\\名称规格单位统一对照表.xlsx";
        excelTemplate.setFileAbsPath(excelPath);
        excelTemplate.setSheetName(sheetName);
        excelTemplate.setHeaderRowCount(1);

        Map<String, MaterialUnifiedRecordBo> originItemNameModelUnitUnifiedMap=new HashMap<>();

        ExcelStringRowTable excelStringRowTable=NslUtilExcelRead.readDataAsLineListOfStringListIncludeHeadRows(excelTemplate,4);
        List<ExcelStringRowTable.ExcelStringRow> rowList=excelStringRowTable.getRowList();
        for (ExcelStringRowTable.ExcelStringRow stringRow : rowList) {
            List<String> stringList=stringRow.getStringList();
            String originItem=stringList.get(2);
            String unifiedItem=stringList.get(3);
            String rateString=stringList.get(4);
            double rate=Double.parseDouble(rateString);
            MaterialUnifiedRecordBo unifiedRecord=new MaterialUnifiedRecordBo();
            unifiedRecord.setConversionRate(rate);
            unifiedRecord.setOriginItemNameModelUnit(originItem);
            unifiedRecord.setUnifiedItemNameModelUnit(unifiedItem);
            originItemNameModelUnitUnifiedMap.put(originItem,unifiedRecord);
        }
        return originItemNameModelUnitUnifiedMap;
    }


    /**
     * 从 多个 定案表中获取 List<MaterialInfoBo>
     * @param excelPathList
     * @param contractMap
     * @return
     */
    static public List<MaterialInfoBo> readMaterialInfoBoListFromMultipleAuditExcel(List<String> excelPathList,Map<String,ProjectInfoBo> contractMap){
        List<MaterialInfoBo> materialInfoBoList=new ArrayList<>();
        excelPathList.forEach(e->{
           List<MaterialInfoBo> subList=readMaterialInfoBoListFromSingleAuditExcel(e,contractMap);
           materialInfoBoList.addAll(subList);
        });

        return materialInfoBoList;
    }

    /**
     * 从 单个 定案表中获取 List<MaterialInfoBo>,版本v2，对应excel文件 审计对照表-汇总_定案表
     * @param excelPath
     * @param sheetName
     * @return
     */
    static public List<MaterialInfoBo> readMaterialInfoBoListFromSingleAuditExcelV2(String excelPath,String sheetName){
        //从 定案材料表中 获取 MaterialInfoBo List
        ExcelTemplate excelTemplate=new ExcelTemplate();
        excelTemplate.setFileAbsPath(excelPath);
        excelTemplate.setSheetName(sheetName);
        excelTemplate.setHeaderRowCount(0);


        //1 获取 名称 规格程式 单位 审定数量 的列号
        String keyWord1="名称";
        String keyWord2="规格";
        String keyWord3="单位";
        String keyWord4="审定-数量";
        String keyWord5="审定-合价";
        String keyWord6="合同编号";
        String keyWord7="单项工程名称";
        List<String> keyWordList=new ArrayList<>();
        keyWordList.add(keyWord1);
        keyWordList.add(keyWord2);
        keyWordList.add(keyWord3);
        keyWordList.add(keyWord4);
        keyWordList.add(keyWord5);
        keyWordList.add(keyWord6);
        keyWordList.add(keyWord7);

        Map<String, NslPointInt> xyMap=new HashMap<>();

        ExcelStringRowTable excelStringRowTable=NslUtilExcelRead.readDataAsLineListOfStringListIncludeHeadRows(excelTemplate,20);
        List<ExcelStringRowTable.ExcelStringRow> rowList=excelStringRowTable.getRowList();
        for (int i = 0; i < rowList.size(); i++) {
            ExcelStringRowTable.ExcelStringRow stringRow =rowList.get(i);
            List<String> stringList=stringRow.getStringList();
            for (int j = 0; j < stringList.size(); j++) {
                String cellString=stringList.get(j);
                for (String s : keyWordList) {
                    addMap(cellString,s,xyMap,j,i);
                }
            }
        }
        int itemNameColumnIndex=xyMap.get(keyWord1).getX();
        int keyItemModelColumnIndex=xyMap.get(keyWord2).getX();
        int keyItemUnitColumnIndex=xyMap.get(keyWord3).getX();
        int keyDecisionCountColumnIndex=xyMap.get(keyWord4).getX();
        int keyItemCostColumnIndex=xyMap.get(keyWord5).getX();
        int keyContractSnColumnIndex=xyMap.get(keyWord6).getX();
        int keySingleProjectNameColumnIndex=xyMap.get(keyWord7).getX();

//        log.info("xyMap = {}",xyMap);
        //获取  有效行的范围,收集 materialInfo
        List<MaterialInfoBo> materialInfoList=new ArrayList<>();
        int beginRowIndex=1;
        for (int i = beginRowIndex; i < rowList.size(); i++) {
            ExcelStringRowTable.ExcelStringRow stringRow =rowList.get(i);
            List<String> stringList=stringRow.getStringList();
            String itemName=stringList.get(itemNameColumnIndex).trim();
            String itemModel=stringList.get(keyItemModelColumnIndex).trim();
            String itemUnit=stringList.get(keyItemUnitColumnIndex).trim();
            String decisionCountString=stringList.get(keyDecisionCountColumnIndex);
            String itemCost=stringList.get(keyItemCostColumnIndex);
            String contractSn=stringList.get(keyContractSnColumnIndex);
            String singleNameProject=stringList.get(keySingleProjectNameColumnIndex);

            //判断是否截止，名称为空，单位为空
            if(itemModel.equals("") && itemUnit.equals("")){
                break;
            }
            double decisionCount=Double.parseDouble(decisionCountString);
//            if(contractSnList.contains(contractSn)){
            //            }
            MaterialInfoBo materialInfo =new MaterialInfoBo();
            materialInfo.setQuantity(decisionCount);
            materialInfo.setItemName(itemName);
            materialInfo.setItemModel(itemModel);
            materialInfo.setItemUnit(itemUnit);
            materialInfo.setItemCost(Double.parseDouble(itemCost));
//            materialInfo.setProjectInfoBo(projectInfoBo);
            materialInfo.setExcelPath(excelPath);
            materialInfo.setSingleProjectName(singleNameProject);
            materialInfo.setContractSn(contractSn);

            materialInfoList.add(materialInfo);
        }

        return materialInfoList;
    }


    /**
     * 从 单个 定案表中获取 List<MaterialInfoBo>
     * 1. 获取单项名称
     * 2. 获取 材料list
     * @param excelPath 定案表 file path
     * @param contractMap  合同 和 项目信息map
     * @return
     */
    static public List<MaterialInfoBo> readMaterialInfoBoListFromSingleAuditExcel(String excelPath,Map<String,ProjectInfoBo> contractMap){
        //获取 单项名称
        String singleProjectName=readSingleProjectNameFromAuditExcel(excelPath);

        // 获得 ProjectInfoBo
        //找到对应的 合同编号，就设置为合同编号，找不到 保持 null
        ProjectInfoBo projectInfoBo=new ProjectInfoBo();
        projectInfoBo.setSingleProjectName(singleProjectName);
        contractMap.values().stream().anyMatch(e->{
            return e.getSingleProjectNameList().stream().anyMatch(p->{
                if(p.trim().equals(singleProjectName)){
                    projectInfoBo.setContractSn(e.getContractSn());
                    return true;
                }
                return false;
            });
        });


        //从 定案材料表中 获取 MaterialInfoBo List
        ExcelTemplate excelTemplate=new ExcelTemplate();
        excelTemplate.setFileAbsPath(excelPath);
        excelTemplate.setSheetName("表四甲主材表");
        excelTemplate.setHeaderRowCount(0);


        //1 获取 名称 规格程式 单位 审定数量 的列号
        String keyWord1="规格程式";
        String keyWord2="单位";
        String keyWord3="Ⅲ";
        String keyWord4="审定数量";
        String keyWord5="审定甲供材料金额";

        List<String> keyWordList=new ArrayList<>();
        keyWordList.add(keyWord1);
        keyWordList.add(keyWord2);
        keyWordList.add(keyWord3);
        keyWordList.add(keyWord4);
        keyWordList.add(keyWord5);

        Map<String, NslPointInt> xyMap=new HashMap<>();

        ExcelStringRowTable excelStringRowTable=NslUtilExcelRead.readDataAsLineListOfStringListIncludeHeadRows(excelTemplate,20);
        List<ExcelStringRowTable.ExcelStringRow> rowList=excelStringRowTable.getRowList();
        for (int i = 0; i < rowList.size(); i++) {
            ExcelStringRowTable.ExcelStringRow stringRow =rowList.get(i);
            List<String> stringList=stringRow.getStringList();
            for (int j = 0; j < stringList.size(); j++) {
                String cellString=stringList.get(j);
                for (String s : keyWordList) {
                    addMap(cellString,s,xyMap,j,i);
                }
            }
        }

        int keyItemModelColumnIndex=xyMap.get(keyWord1).getX();
        int keyItemUnitColumnIndex=xyMap.get(keyWord2).getX();
        int keyDecisionCountColumnIndex=xyMap.get(keyWord4).getX();
        int itemNameColumnIndex=keyItemModelColumnIndex-1;
        int keyItemCostColumnIndex=xyMap.get(keyWord5).getX();

        //获取  有效行的范围,收集 materialInfo
        List<MaterialInfoBo> materialInfoList=new ArrayList<>();
        int beginRowIndex=xyMap.get(keyWord3).getY()+1;
        for (int i = beginRowIndex; i < rowList.size(); i++) {
            ExcelStringRowTable.ExcelStringRow stringRow =rowList.get(i);
            List<String> stringList=stringRow.getStringList();
            String itemName=stringList.get(itemNameColumnIndex).trim();
            String itemModel=stringList.get(keyItemModelColumnIndex).trim();
            String itemUnit=stringList.get(keyItemUnitColumnIndex).trim();
            String decisionCountString=stringList.get(keyDecisionCountColumnIndex);
            String itemCost=stringList.get(keyItemCostColumnIndex);

            //判断是否截止，名称为空，单位为空
            if(itemModel.equals("") && itemUnit.equals("")){
                break;
            }
            double decisionCount=Double.parseDouble(decisionCountString);
            MaterialInfoBo materialInfo =new MaterialInfoBo();
            materialInfo.setQuantity(decisionCount);
            materialInfo.setItemName(itemName);
            materialInfo.setItemModel(itemModel);
            materialInfo.setItemUnit(itemUnit);
            materialInfo.setItemCost(Double.parseDouble(itemCost));
            materialInfo.setProjectInfoBo(projectInfoBo);
            materialInfo.setExcelPath(excelPath);
            materialInfoList.add(materialInfo);
        }


        return materialInfoList;

    }


    /**
     * 获取审查表的 单项名称
     * @param excelPath
     * @return
     */
    static private String readSingleProjectNameFromAuditExcel(String excelPath){
        StringBuffer singleProjectNameStringBuff=new StringBuffer();

        ExcelTemplate excelTemplate=new ExcelTemplate();
//        String excelPath="Z:\\GS_Dir_Zdisk\\GS#i3#Work\\2025SJY\\公司内部支撑\\风控\\定案+2021年中国联通河南驻马店张仲景大药房等140个大客户接入项目(线路类EPC)-新蔡.xls";
        excelTemplate.setFileAbsPath(excelPath);
        excelTemplate.setSheetName("表一");
        excelTemplate.setHeaderRowCount(0);

        ExcelStringRowTable excelStringRowTable=NslUtilExcelRead.readDataAsLineListOfStringListIncludeHeadRows(excelTemplate,10);

        String keyWord1="单项工程名称:";
        String keyWord2="建设单位名称:";

        ProjectInfoBo projectInfoBo =new ProjectInfoBo();

        excelStringRowTable.getRowList().forEach(e->{
            for (String s : e.getStringList()) {
                if(s.contains(keyWord1) && s.contains(keyWord2)){
                    int begin=s.indexOf(keyWord1)+keyWord1.length();
                    int end=s.indexOf(keyWord2);
//                    System.out.println(s.substring(begin, end).trim());
                    singleProjectNameStringBuff.append(s.substring(begin, end).trim());
                }
            }
        });
        return singleProjectNameStringBuff.toString();
    }

    static private void addMap(String cellString,String keyword,Map<String,NslPointInt> xyMap,int columnIndex,int rowIndex){
        if(cellString.equals(keyword)){
            NslPointInt pointInt=new NslPointInt();
            pointInt.setX(columnIndex);
            pointInt.setY(rowIndex);
            xyMap.put(keyword,pointInt);
        }
    }

    /**
     * 读取 oa 表的材料信息
     */
    static public List<MaterialInfoBo> readOaMaterialInfoOfOa(String oaExcelPath,String oaSheetName,List<String> contractSnList){


        List<String> lowerTrimContractSnList=contractSnList.stream().map((e)->{
            String contractSn=e.trim().toLowerCase();
            return contractSn;
        }).collect(Collectors.toList());

        ExcelTemplate excelTemplate=new ExcelTemplate();
        excelTemplate.setFileAbsPath(oaExcelPath);
        excelTemplate.setSheetName(oaSheetName);
        excelTemplate.setHeaderRowCount(0);
        //String excelPath="Z:\\GS_Dir_Zdisk\\GS#i3#Work\\2025SJY\\公司内部支撑\\风控\\项目物资核算台账20230714092239_OA导出表_v2.xlsx";
//        String contractSn="ZC-DD-SR-2021-01124";
        //String sheetName="项目物资核算台账";

//        ProjectInfoBo projectInfoBo =new ProjectInfoBo();
//        projectInfoBo.setContractSn(contractSn);
//        projectInfoBo.setSingleProjectName(singleProjectName);

        Set<String> keySet=new HashSet<>();
        String keyItemName="物资名称";
        String keyItemModel="规格";
        String keyItemUnit="单位";
        String keyQuantity="匹配数量";
        String keyContractSn="收入合同编号";
        String keyItemCost="匹配金额";
        keySet.add(keyItemName);
        keySet.add(keyItemModel);
        keySet.add(keyItemUnit);
        keySet.add(keyQuantity);
        keySet.add(keyContractSn);
        keySet.add(keyItemCost);


        ExcelStringRowTable excelStringRowTable=NslUtilExcelRead.readDataAsLineListOfStringListIncludeHeadRows(excelTemplate,29);

        Map<String,NslPointInt> keyMap=NslUtilExcelCommonUtils.findKeyWordCoordinate(keySet,excelStringRowTable,1,27);

//        System.out.println(keyMap);

        int itemNameIndex=keyMap.get(keyItemName).getX();
        int itemModelIndex=keyMap.get(keyItemModel).getX();
        int itemUnitIndex=keyMap.get(keyItemUnit).getX();
        int itemQuantityIndex=keyMap.get(keyQuantity).getX();
        int contractSnIndex=keyMap.get(keyContractSn).getX();
        int itemCostIndex=keyMap.get(keyItemCost).getX();

        List<MaterialInfoBo> materialInfoList=new ArrayList<>();

        List<ExcelStringRowTable.ExcelStringRow> rowList=excelStringRowTable.getRowList();
        for (int i = 1; i < rowList.size(); i++) {
            ExcelStringRowTable.ExcelStringRow row =rowList.get(i);
            List<String> stringList=row.getStringList();
            String contractSnRead=stringList.get(contractSnIndex).trim().toLowerCase();
            if(contractSnRead !=null && lowerTrimContractSnList.contains(contractSnRead)){
                String itemName=stringList.get(itemNameIndex);
                String itemModel=stringList.get(itemModelIndex);
                String itemUnit=stringList.get(itemUnitIndex);
                String itemQuantity=stringList.get(itemQuantityIndex);
                String itemCost=stringList.get(itemCostIndex);

                MaterialInfoBo materialInfo=new MaterialInfoBo();
                materialInfo.setItemName(itemName);
                materialInfo.setItemModel(itemModel);
                materialInfo.setItemUnit(itemUnit);
                materialInfo.setQuantity(Double.parseDouble(itemQuantity));
                materialInfo.setItemCost(Double.parseDouble(itemCost));
                materialInfo.setContractSn(contractSnRead);
                materialInfoList.add(materialInfo);
//                materialInfo.setProjectInfoBo(projectInfoBo);

            }

        }

//        System.out.println(materialInfoList);
//        materialInfoList.forEach(System.out::println);
        return materialInfoList;
    }

    /**
     * 根据 统一对照表，更新 materialInfoList
     * @param materialInfoList
     * @param unifiedRecordMap
     */
    static  public void unifyMaterialList(List<MaterialInfoBo> materialInfoList,Map<String, MaterialUnifiedRecordBo> unifiedRecordMap){
        List<String> unknownOriginItemList=new ArrayList<>();
        for (MaterialInfoBo materialInfo : materialInfoList) {
            String originItem= materialInfo.getItemNameModelUnit();
//            System.out.println(originItem);
            MaterialUnifiedRecordBo unifiedItem=unifiedRecordMap.get(originItem);
            if(unifiedItem!=null){
//                throw new RuntimeException("名称规格单位 = "+originItem+" 在统一对照表中无记录");
                materialInfo.setUnifiedItemNameModelUnit(unifiedItem.getUnifiedItemNameModelUnit());
                materialInfo.setUnifiedQuantity(unifiedItem.getConversionRate()* materialInfo.getQuantity());
            }else{
                materialInfo.setUnifiedItemNameModelUnit("未统一_"+originItem);
                materialInfo.setUnifiedQuantity(1* materialInfo.getQuantity());
            }
        }
    }

    /**
     * 构建 去重后的 统一 名称规划单位
     * @param materialInfoListOfOa
     * @param materialInfoListOfDecision
     * @return
     */
    static  public Set<String> buildUnifiedItemNameModelUnitOfMaterialInfo(List<MaterialInfoBo> materialInfoListOfOa,List<MaterialInfoBo> materialInfoListOfDecision){
        Set<String> unifiedItemNameModelUnitSet=new HashSet<>();
        materialInfoListOfDecision.forEach(e->{
            unifiedItemNameModelUnitSet.add(e.getUnifiedItemNameModelUnit());
        });
        materialInfoListOfOa.forEach(e->{
            unifiedItemNameModelUnitSet.add(e.getUnifiedItemNameModelUnit());
        });
        return unifiedItemNameModelUnitSet;
    }
}
